/*
 * Copyright (C) 2009 codemobiles.com.
 * http://www.codemobiles.com
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * author: Chaiyasit Tayabovorn 
 * email: chaiyasit.t@gmail.com
 */

package com.codemobiles.droidslator.dicts;

import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.codemobiles.droidslator.ETLexBean;
import com.codemobiles.droidslator.TELexBean;
import com.codemobiles.droidslator.util.MyUtil;

public class DBAdapter {
  private final String ETLEX_ID = "_id";
  private final String ETLEX_TENTRY = "tentry";
  private final String ETLEX_ETHAI = "ethai";
  private final String ETLEX_ESYN = "esyn";
  private final String ETLEX_ECAT = "ecat";
  private final String ETLEX_EANT = "eant";

  public final String TELEX_ID = "_id";
  public final String TELEX_EENTRY = "eentry";
  public final String TELEX_TCAT = "tcat";
  public final String TELEX_TSYN = "tsyn";
  public final String TELEX_TSAMPLE = "tsample";
  public final String TELEX_TANT = "tant";

  private final String TAG = "DBAdapter";
  private final String DATABASE_FILE = "/sdcard/.dsdb";
  private final String ET_TABLES = "etlex_";
  private final String TE_TABLES = "telex_";

  private SQLiteDatabase db;

  public DBAdapter() {
  }

  /**
   * @author preetam.palwe
   * 
   */
  public static class DBUtil {
    public static void safeCloseCursor(Cursor cursor) {
      if (cursor != null) {
        cursor.close();
      }
    }

    public static void safeCloseDataBase(SQLiteDatabase database) {
      if (database != null) {
        database.close();
      }
    }
  }

  // ---opens the database---
  public DBAdapter open() throws Exception {

    db = SQLiteDatabase.openDatabase(DATABASE_FILE, null, SQLiteDatabase.OPEN_READWRITE);
    return this;
  }

  public void close() {
    DBUtil.safeCloseDataBase(db);
  }

  public List<TELexBean> searchTE(final String word, String tableName) throws Exception {

    List<TELexBean> result = new ArrayList<TELexBean>();
    open();
    try {

      Hashtable<String, String> thaiLetters = MyUtil.getThaiLettersHashTbl();
      final String firstLetter = word.substring(1, 2);
      String position = thaiLetters.get(firstLetter);

      tableName = tableName + position;
      Log.i("Droidslator: TableName ", tableName);

      Cursor mCursor = db.query(true, tableName, new String[] { TELEX_ID, TELEX_EENTRY, TELEX_TCAT, TELEX_TSYN, TELEX_TSAMPLE, TELEX_TANT }, TELEX_ID
          + " like " + word, null, null, null, null, null);
      if (mCursor != null) {
        mCursor.moveToFirst();
        do {
          TELexBean bn = new TELexBean();
          bn.setTsearch(mCursor.getString(mCursor.getColumnIndex(TELEX_ID)));
          bn.setEentry(mCursor.getString(mCursor.getColumnIndex(TELEX_EENTRY)));
          bn.setTcat(mCursor.getString(mCursor.getColumnIndex(TELEX_TCAT)));
          bn.setTsyn(mCursor.getString(mCursor.getColumnIndex(TELEX_TSYN)));
          bn.setTsample(mCursor.getString(mCursor.getColumnIndex(TELEX_TSAMPLE)));
          bn.setTant(mCursor.getString(mCursor.getColumnIndex(TELEX_TANT)));
          result.add(bn);
        } while (mCursor.moveToNext());
      }
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      close();
    }
    return result;
  }

  public String searchTELex(String word) throws Exception {

    String res = "";
    List<TELexBean> result = searchTE("'" + word + "'", TE_TABLES);
    TELexBean bn;
    for (int i = 0; i < result.size(); i++) {
      bn = result.get(i);
      res = res + extractTE(bn) + "\n";
    }
    if (res.equals("")) {
      res = "";
    }
    return res;
  }

  /**
   * @param word
   * @return
   * @throws Exception
   */
  public List<ETLexBean> searchET(final String word) throws Exception {

    List<ETLexBean> result = new ArrayList<ETLexBean>();
    open();
    try {

      Cursor mCursor = db.query(true, ET_TABLES + word.substring(1, 2).toLowerCase(), new String[] { ETLEX_ID, ETLEX_TENTRY, ETLEX_ETHAI, ETLEX_ESYN,
          ETLEX_EANT, ETLEX_ECAT }, ETLEX_ID + " like " + word, null, null, null, null, "20");
      if (mCursor != null) {
        mCursor.moveToFirst();
        do {
          ETLexBean bn = new ETLexBean();
          bn.setId(mCursor.getString(mCursor.getColumnIndex(ETLEX_ID)));
          // bn.setEentry(mCursor.getString(mCursor.getColumnIndex(ETLEX_EENTRY)));
          bn.setTentry(mCursor.getString(mCursor.getColumnIndex(ETLEX_TENTRY)));
          bn.setEthai(mCursor.getString(mCursor.getColumnIndex(ETLEX_ETHAI)));
          bn.setEsyn(mCursor.getString(mCursor.getColumnIndex(ETLEX_ESYN)));
          bn.setEant(mCursor.getString(mCursor.getColumnIndex(ETLEX_EANT)));
          bn.setEcat(mCursor.getString(mCursor.getColumnIndex(ETLEX_ECAT)));
          result.add(bn);
        } while (mCursor.moveToNext());
      }
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      close();
    }
    return result;
  }

  public List<String> preSearchTE(String word, List<String> result) throws Exception {

    word = "'" + word + "%'";
    open();
    try {
      String mTableSubfix = MyUtil.getThaiLettersHashTbl().get(word.substring(1, 2).toLowerCase());
      Cursor mCursor = db.query(true, TE_TABLES + mTableSubfix, new String[] { TELEX_ID, TELEX_EENTRY }, TELEX_ID + " like " + word, null, null,
          null, null, "50");
      if (mCursor != null) {
        mCursor.moveToFirst();
        do {

          String mTELEX_ID = mCursor.getString(mCursor.getColumnIndex(TELEX_ID));
          String mTELEX_TENTRY = mCursor.getString(mCursor.getColumnIndex(TELEX_EENTRY));
          result.add(mTELEX_ID + " : " + mTELEX_TENTRY);
        } while (mCursor.moveToNext());
      }
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      close();
    }
    return result;
  }

  /**
   * @param word
   * @return
   * @throws Exception
   */
  public List<String> preSearchETV1(String word, List<String> result) throws Exception {

    word = "'" + word + "%'";
    open();
    try {

      Cursor mCursor = db.query(true, ET_TABLES + word.substring(1, 2).toLowerCase(), new String[] { ETLEX_ID, ETLEX_TENTRY }, ETLEX_ID + " like "
          + word, null, null, null, null, "50");
      if (mCursor != null) {
        mCursor.moveToFirst();
        do {

          String mETLEX_ID = mCursor.getString(mCursor.getColumnIndex(ETLEX_ID));
          String mETLEX_TENTRY = mCursor.getString(mCursor.getColumnIndex(ETLEX_TENTRY));
          result.add(mETLEX_ID + " : " + mETLEX_TENTRY);
        } while (mCursor.moveToNext());
      }
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      close();
    }
    return result;
  }

  public String searchETV1(String word) throws Exception {

    String res = "";
    List<ETLexBean> result = searchET("'" + word + "'");
    ETLexBean bn;
    for (int i = 0; i < result.size(); i++) {
      bn = result.get(i);
      res = res + extractET(bn) + "\n";
    }
    if (res.equals("")) {
      res = "";
    }
    return res;
  }

  public String extractET(ETLexBean bn) {
    String res = "";

    res = "<b><font color=#cc0033>" + bn.getId() + "</b></font> <font color=#006699>[" + bn.getEcat() + "] </font>" + bn.getTentry() + "\n";
    if (bn.getEsyn() != null)
      res = res + "Syn. " + bn.getEsyn() + "\n";
    if (bn.getEant() != null)
      res = res + "Related. " + bn.getEant();
    return res;
  }

  public String extractTE(TELexBean bn) {
    String res = "";

    res = "<b><font color=#cc0033>" + bn.getTsearch() + "</b></font> <font color=#006699>[" + bn.getTcat() + "] </font>" + bn.getEentry() + "\n";
    if (bn.getTsample() != null)
      res = res + "Sample. " + bn.getTsample() + "\n";
    if (bn.getTsyn() != null)
      res = res + "Syn. " + bn.getTsyn() + "\n";
    if (bn.getTant() != null)
      res = res + "Related. " + bn.getTant();
    return res;
  }
}
